/*
============================================================================
 Name        : BengBengDbMgr.cpp
 Author      : 
 Version     :
 Copyright   : Your copyright notice
 Description : CBengBengDbMgr implementation
============================================================================
*/

#include "BengBengDbMgr.h"
#include "DbEngine.h"
#include "FileCommFun.h"
#include "StringCommFun.h"

//sql\xD3\xEF\xBE\xE4\xB5\xC4\xD7\xEE\xB4\x{f3936}\xC8
#define KMAX_SQL_LENGTH 1024

CBengBengDbMgr::CBengBengDbMgr()
{
	// No implementation required
}


CBengBengDbMgr::~CBengBengDbMgr()
{
	if (iDbEngine != NULL)
	{
		delete iDbEngine;
		iDbEngine = NULL;
	}
}

CBengBengDbMgr* CBengBengDbMgr::NewLC()
{
	CBengBengDbMgr* self = new (ELeave)CBengBengDbMgr();
	CleanupStack::PushL(self);
	self->ConstructL();
	return self;
}

CBengBengDbMgr* CBengBengDbMgr::NewL()
{
	CBengBengDbMgr* self=CBengBengDbMgr::NewLC();
	CleanupStack::Pop(); // self;
	return self;
}

void CBengBengDbMgr::ConstructL()
{
	iDbEngine = CDbEngine::NewL(FileCommFun::BengBengMessageFilePath());

	_LIT(KDbName, "bengbeng.db");
	TFileName dbName(KDbName);
	if(iDbEngine->DatabaseExist(dbName))
	{
		iDbEngine->OpenDatabaseL(dbName);
	}
	else
	{
		iDbEngine->CreateDatabaseL(dbName);
	}

	CreateBengBengMessageTableL();
}

TBool CBengBengDbMgr::CreateBengBengMessageTableL()
{
	//\xB6\xA8\xD2\xE5\xD7\x{05b6}\xCE\xC3\xFB
	_LIT(K_COL_TIME,       "Time");
	_LIT(K_COL_TITLE,      "Title");
	_LIT(K_COL_DETAIL,     "DetailText");
	_LIT(K_TABLENAME,      "BengBengMessage");

	//\xB6\xA8\xD2\xE5\xB1\xED\xBD\x{1e79}
	CDbColSet* columns = CDbColSet::NewLC();

	TDbCol time(K_COL_TIME, EDbColInt32);
	(void)columns->AddL(time);

	TDbCol title(K_COL_TITLE, EDbColText);
	(void)columns->AddL(title);

	TDbCol detailText(K_COL_DETAIL, EDbColLongText);
	(void)columns->AddL(detailText);

	//\xBD\xA8\xB1\xED
	TBool ret = iDbEngine->CreateTableFromColSet(K_TABLENAME, *columns);
	CleanupStack::PopAndDestroy(columns);

	return ret;
}

TBool CBengBengDbMgr::InsertBengBengMessageL(TInt32 aTime, const TDesC& aTitle, const TDesC& aDetailText)
{
	TBuf<KMAX_SQL_LENGTH> title(aTitle);

	TInt len = aDetailText.Length();
	HBufC* detail = HBufC::NewLC(len);
	TPtr detailPtr = detail->Des();
	detailPtr.Copy(aDetailText);

	StringCommFun::FormatSqlString(title);
	StringCommFun::FormatSqlString(detailPtr);

	//TBuf<KMAX_SQL_LENGTH> sql;
	len = aTitle.Length() + aDetailText.Length() + 128;
	HBufC* sql = HBufC::NewLC(len);
	TPtr sqlPtr = sql->Des();
	sqlPtr.Copy(aDetailText);

	_LIT(KInsertSql, "Insert into BengBengMessage (Time, Title, DetailText) values (%d, '%S', '%S')");
	sqlPtr.Format(KInsertSql, aTime, &title, &detailPtr);        

	TBool ret = iDbEngine->ExecuteSQL(sqlPtr);
	CleanupStack::PopAndDestroy(2);

	return ret;
}

TBool CBengBengDbMgr::QueryBengBengMessageL(TInt aNum, TDes& aTitle, HBufC* &aDetail)
{
	TInt time = 0;
	if (!QueryBengBengMessageTimeL(aNum, time))
	{
		return EFalse;
	}

	TBuf<KMAX_SQL_LENGTH> sql;
	_LIT(K_Query_SQL, "select * from BengBengMessage where Time = %d");
	sql.Format(K_Query_SQL, time);

	if(iDbEngine->ExecuteQuerySQLL(sql))
	{
		TPtrC title;
		TInt time;
		(void)iDbEngine->GetTInt(1, time);
		(void)iDbEngine->GetTDes(2, title);
		aTitle.Zero();
		aTitle.Copy(title);

		if (aDetail != NULL)
		{
			delete aDetail;
			aDetail = NULL;
		}
		sql.Zero();
		_LIT(K_QueryDetail_SQL, "select DetailText from BengBengMessage where Time = %d");
		sql.Format(K_QueryDetail_SQL, time);
		(void)iDbEngine->ReadLongColumns(_L("BengBengMessage"), sql, _L("DetailText"), aDetail);
		TInt len = aDetail->Length();

		return ETrue;
	}
	return EFalse;
}

TBool CBengBengDbMgr::DelFirstBengBengMessageL()
{
	TInt time = 0;
	if (!QueryBengBengMessageTimeL(1, time))
	{
		return EFalse;
	}

	//\x{027e}\xB3\xFDSQL\xD3\xEF\xBE\xE4
	_LIT(K_DELETE_SQL, "delete from BengBengMessage where Time = %d");
	TBuf<KMAX_SQL_LENGTH> sql;
	sql.Format(K_DELETE_SQL, time);

	//\x{05b4}\xD0\x{0432}\xE9\x{046f}\xD3\xEF\xBE\xE4
	return iDbEngine->ExecuteSQL(sql);
}

TBool CBengBengDbMgr::DelBengBengMessageL(TInt aTime)
{
	TBuf<KMAX_SQL_LENGTH> sql;
	if (aTime == 0)//delete all
	{
		//\x{027e}\xB3\xFDSQL\xD3\xEF\xBE\xE4
		_LIT(K_DELETE_SQL, "delete from BengBengMessage");	
		sql.Copy(K_DELETE_SQL);
	} 
	else
	{
		//\x{027e}\xB3\xFDSQL\xD3\xEF\xBE\xE4
		_LIT(K_DELETE_SQL, "delete from BengBengMessage where Time = %d");
		sql.Format(K_DELETE_SQL, aTime);
	}

	//\x{05b4}\xD0\x{0432}\xE9\x{046f}\xD3\xEF\xBE\xE4
	return iDbEngine->ExecuteSQL(sql);
}

TBool CBengBengDbMgr::GetBengBengMessgeCount(TInt& aCount)
{
	//\xB2\xE9\x{046f}SQL\xD3\xEF\xBE\xE4
	_LIT(K_SELECT_SQL, "select Time from BengBengMessage");

	//\x{05b4}\xD0\x{0432}\xE9\x{046f}\xD3\xEF\xBE\xE4
	TBuf<KMAX_SQL_LENGTH> selectSql(K_SELECT_SQL);
	if(!iDbEngine->ExecuteQuerySQLL(selectSql))
	{
		return EFalse;
	}

	//\xBC\xC7\x{00bc}\xD7\xDC\xCA\xFD
	aCount = iDbEngine->TotalRowCount();

	iDbEngine->EndQuery();

	return ETrue;
}

TBool CBengBengDbMgr::BengBengMessageIsExist(TInt aTime)
{
	//\xC9\xFA\xB3\x{0272}\xE9\x{046f}\xD3\xEF\xBE\xE4
	_LIT(K_SELECT_SQL, "select Time from BengBengMessage where Time = %d");
	TBuf<KMAX_SQL_LENGTH> sql;
	sql.Format(K_SELECT_SQL, aTime);

	//\x{05b4}\xD0\x{0432}\xE9\x{046f}\xD3\xEF\xBE\xE4
	TBool ret = iDbEngine->ExecuteQuerySQLL(sql);
	if(!ret)
	{
		return EFalse;
	}

	//\xBC\xC7\x{00bc}\xD7\xDC\xCA\xFD
	TInt count = iDbEngine->TotalRowCount();
	if(0 == count)
	{
		return EFalse;
	}
	return ETrue;
}

TBool CBengBengDbMgr::QueryBengBengMessageTimeL(TInt aN, TInt& aTime)
{
	TInt count = 0;
	GetBengBengMessgeCount(count);

	TBuf<KMAX_SQL_LENGTH> sql;
	_LIT(K_Query_SQL, "select Time from BengBengMessage");
	sql.Copy(K_Query_SQL());

	if(iDbEngine->ExecuteQuerySQLL(sql))
	{
		//\xBC\xC7\x{00bc}\xD7\xDC\xCA\xFD
		TInt count = iDbEngine->TotalRowCount();
		if (aN > count)
		{
			return EFalse;
		}
		
		iDbEngine->GotoFirstRowL();
		for (TInt i = 1; i < aN; i++)
		{
			iDbEngine->GotoNextRowL();
		}
		(void)iDbEngine->GetTInt(1, aTime);

		return ETrue;
	}
	return EFalse;
}
